package jp.ac.u_tokyo.dch.dao.front;

import java.util.ArrayList;
import java.util.List;

import jp.ac.u_tokyo.dch.core.db.CustomPreparedStatement;
import jp.ac.u_tokyo.dch.core.db.CustomResultSet;
import jp.ac.u_tokyo.dch.core.db.DBConnection;
import jp.ac.u_tokyo.dch.core.exceptions.DBException;
import jp.ac.u_tokyo.dch.model.db.DataDisplays;
import jp.ac.u_tokyo.dch.util.common.CommonDBConnection;


/**
* @version 1.1
*/
public class DataDisplaysDao {

	/**
	 * DBコネクションオブジェクト
	 */
	private DBConnection dbconn = null;

	/**
	 * 生成者
	 * @param DBコネクションオブジェクト
	 */
	public DataDisplaysDao(DBConnection dbconn){
		this.dbconn = dbconn;
	}

	/**
	 * データ表示詳細テーブルからリストを取得する
	 * @param スキーマID
	 * @return 表示項目リスト
	 * @throws DBException
	 */
	public List<DataDisplays> selectByKeyId(String id) throws DBException {

		List<DataDisplays> list = new ArrayList<DataDisplays>();
		CustomPreparedStatement ps = null;
		CustomResultSet rs = null;
		try{
			String query = "select * from data_displays where keyschemaid = ? order by displayorder";

			ps = dbconn.prepareStatement(query);

			ps.setInt(1, Integer.valueOf(id));

			rs = ps.executeQuery();
			while (rs.next()){

				DataDisplays dataDisplays = new DataDisplays();
				dataDisplays.setId(rs.getInt("id"));
				dataDisplays.setKeyschemaId(rs.getInt("keyschemaid"));
				dataDisplays.setName(rs.getString("name"));
				dataDisplays.setOrder(rs.getInt("displayorder"));

				list.add(dataDisplays);
			}
		}catch(DBException dbe){
			throw dbe;
		}finally{
			CommonDBConnection.closeResource(ps, rs);
		}
		return list;

	}

	public DataDisplays selectById(String id) throws DBException {

		DataDisplays dataDisplays = null;
		CustomPreparedStatement ps = null;
		CustomResultSet rs = null;
		try{
			String query = "select * from data_displays where id = ?";

			ps = dbconn.prepareStatement(query);

			ps.setInt(1, Integer.valueOf(id));

			rs = ps.executeQuery();
			if (rs.next()){

				dataDisplays = new DataDisplays();
				dataDisplays.setId(rs.getInt("id"));
				dataDisplays.setKeyschemaId(rs.getInt("keyschemaid"));
				dataDisplays.setName(rs.getString("name"));
				dataDisplays.setOrder(rs.getInt("displayorder"));

			}
		}catch(DBException dbe){
			throw dbe;
		}finally{
			CommonDBConnection.closeResource(ps, rs);
		}
		return dataDisplays;

	}

	public DataDisplays selectById(Integer keyschemaid, Integer displayorder) throws DBException {

		DataDisplays dataDisplays = null;
		CustomPreparedStatement ps = null;
		CustomResultSet rs = null;
		try{
			String query = "select * from data_displays where keyschemaid = ? and displayorder = ?";

			ps = dbconn.prepareStatement(query);
			int index = 1;
			ps.setInt(index++, keyschemaid);
			ps.setInt(index++, displayorder);

			rs = ps.executeQuery();
			if (rs.next()){

				dataDisplays = new DataDisplays();
				dataDisplays.setId(rs.getInt("id"));
				dataDisplays.setKeyschemaId(rs.getInt("keyschemaid"));
				dataDisplays.setName(rs.getString("name"));
				dataDisplays.setOrder(rs.getInt("displayorder"));
			}
		}catch(DBException dbe){
			throw dbe;
		}finally{
			CommonDBConnection.closeResource(ps, rs);
		}
		return dataDisplays;

	}

	/**
	 * データ表示詳細テーブルに挿入する
	 * @param keyschemaid キースキーマID
	 * @param frontInfo フロント表示オブジェクト
	 * @return 挿入情報
	 * @throws DBException 例外
	 */
	public int insert(Integer id, DataDisplays dataDisplays) throws DBException {

		CustomPreparedStatement ps = null;
		int result = 0;
		try{
			String query = "INSERT INTO data_displays " +
					"(id,keyschemaid,name,displayorder) " +
					"select ?,?,?,case  when max(displayorder) IS NULL then '1' else max(displayorder) + 1 end as maxorder" +
					" from data_displays where keyschemaid = ?";

			ps = dbconn.prepareStatement(query);
			int index = 1;

			ps.setInt(index++, id);
			ps.setInt(index++, dataDisplays.getKeyschemaId());
			ps.setString(index++, dataDisplays.getName());
			ps.setInt(index++, dataDisplays.getKeyschemaId());
			result = ps.executeUpdate();
		}catch(DBException dbe){
			dbe.printStackTrace();
			throw dbe;
		}finally{
			CommonDBConnection.closeResource(ps);
		}
		return result;
	}

	public int updateOrder(Integer id, Integer updateOrder) throws DBException {

		CustomPreparedStatement ps = null;
		int result = 0;
		try{
			String query = "UPDATE data_displays SET displayorder = ? WHERE id = ?";

			ps = dbconn.prepareStatement(query);
			int index = 1;
			ps.setInt(index++, updateOrder);
			ps.setInt(index++, id);
			result = ps.executeUpdate();
		}catch(DBException dbe){
			dbe.printStackTrace();
			throw dbe;
		}finally{
			CommonDBConnection.closeResource(ps);
		}
		return result;
	}

	/**
	 * データ表示テーブルに更新する
	 * @param dataDisplays フロント表示オブジェクト
	 * @return 挿入情報
	 * @throws DBException 例外
	 */
	public int update(DataDisplays dataDisplays) throws DBException {

		CustomPreparedStatement ps = null;
		int result = 0;
		try{
			String query = "UPDATE data_displays SET name = ? WHERE id = ?";

			ps = dbconn.prepareStatement(query);
			int index = 1;
			ps.setString(index++, dataDisplays.getName());
			ps.setInt(index++, dataDisplays.getId());
			result = ps.executeUpdate();
		}catch(DBException dbe){
			dbe.printStackTrace();
			throw dbe;
		}finally{
			CommonDBConnection.closeResource(ps);
		}
		return result;
	}

	/***
	 * 削除する
	 * @param keyschemaid キースキーマID
	 * @return 削除件数
	 * @throws DBException 例外
	 */
	public int delete(String id) throws DBException {

		CustomPreparedStatement ps = null;
		int result = 0;
		try{
			String query = "DELETE FROM data_displays WHERE id = ?";
			ps = dbconn.prepareStatement(query);
			ps.setInt(1, Integer.valueOf(id));
			result = ps.executeUpdate();
		}catch(DBException dbe){
			throw dbe;
		}finally{
			CommonDBConnection.closeResource(ps);
		}
		return result;
	}
}
